package tgc.tdu.tgq.demo.service;

import java.util.List;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import tgc.tdu.tgq.demo.custom.BaseService;
import tgc.tdu.tgq.demo.entity.Course;
import tgc.tdu.tgq.demo.entity.Grade;
import tgc.tdu.tgq.demo.repository.GradeRepository;

@Service
public class GradeService extends BaseService<Grade, Integer>{
	@Autowired
	private GradeRepository gradeDAO;
	@Resource
	private EntityManager entityManager;
	
	public List<Grade> findByStudentId(Integer id) {	
		return gradeDAO.findByStudentId(id);
	}

	public Grade findByStudentIdAndCourseId(Integer bjid, Integer kcId) {
		return gradeDAO.findByStudentIdAndCourseId(bjid,kcId);
	}

	public List<Course> findCourseByTbClassId(Integer bjId) {
		return gradeDAO.findCourseByTbClassId(bjId);
	}

	
	 public List<Grade> findByTbClassIdAndCourseId(Integer bjId, Integer couId) {
	 return gradeDAO.findByTbClassIdAndCourseId( bjId, couId); }

	public List<Grade> findByTbClassIdAndStudnetId(Integer bjId, Integer stuId) {
		return gradeDAO.findByTbClassIdAndStudnetId( bjId, stuId);
	}

	public List<List<Object>> findAllGradeByTbClassId(List<Course> courses, Integer bjId) {
		String hql=" select t.student.name ";
		for (Course course : courses) {
			hql+=",sum(case when t.course.id = "+course.getId()+" then t.grade else 0 end)";			
		}
		hql+=",sum(t.grade) as total";
		hql+=" from Grade t";
		hql+=" where t.student.tbClass.id="+bjId;
		hql+=" group by t.student.id";
		hql+=" order by total desc";
		Query query = entityManager.createQuery(hql);
		entityManager.close();
		return query.getResultList();
	}

	public List<List<Object>> getCjb(Integer bjId, List<Course> courses) {
		String hql=" select t.student.stuNo,t.student.name " ;
		for(Course course : courses) {
			hql+=",	sum(case when t.course.id="+course. getId()+"then t.grade else 0 end)";
		}
			hql+=" ,sum(t.grade) as total";
			hql+=" from Grade t";
			hql+=" where t.student.tbClass.id=" +bjId;
			hql+=" group by t.student.id";
			hql+=" order by total desc";
			Query query=entityManager .createQuery(hql);
			entityManager.close();
			return query.getResultList();

	}
	 
}
